/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     2019/2/13 20:38:00                           */
/*==============================================================*/


if exists (select 1
            from  sysindexes
           where  id    = object_id('login_log')
            and   name  = 'reference_1_fk'
            and   indid > 0
            and   indid < 255)
   drop index login_log.reference_1_fk
go

if exists (select 1
            from  sysobjects
           where  id = object_id('login_log')
            and   type = 'U')
   drop table login_log
go

if exists (select 1
            from  sysobjects
           where  id = object_id('sys_user')
            and   type = 'U')
   drop table sys_user
go

/*==============================================================*/
/* Table: login_log                                             */
/*==============================================================*/
create table login_log (
   log_id               numeric(10)          identity,
   sys_user_id          numeric(11)          null,
   user_id              int                  null default null,
   login_ip             char(15)             null,
   login_time           datetime             null,
   constraint pk_login_log primary key nonclustered (log_id),
   constraint ak_login_ip_login_lo unique (login_ip, login_time),
   constraint ak_user_id_login_lo unique (user_id)
)
go

/*==============================================================*/
/* Index: reference_1_fk                                        */
/*==============================================================*/
create index reference_1_fk on login_log (
sys_user_id asc
)
go

/*==============================================================*/
/* Table: sys_user                                              */
/*==============================================================*/
create table sys_user (
   user_id              numeric(11)          identity,
   username             varchar(50)          null,
   password             varchar(80)          null,
   real_name            varchar(20)          null,
   id_card              char(18)             null,
   birthday             datetime             null default null,
   age                  int                  null default null,
   sex                  int                  null default null,
   address              varchar(255)         null,
   mobile               varchar(15)          null,
   telephone            varchar(20)          null,
   email                varchar(80)          null,
   type                 int                  null default null,
   state                int                  null default null,
   gmt_create           datetime             null,
   gmt_modified         datetime             null,
   constraint pk_sys_user primary key nonclustered (user_id)
)
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('sys_user')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'birthday')
)
begin
   declare @currentuser sysname
select @currentuser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @currentuser, 'table', 'sys_user', 'column', 'birthday'

end


select @currentuser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '生日，年月日',
   'user', @currentuser, 'table', 'sys_user', 'column', 'birthday'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('sys_user')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'age')
)
begin
   declare @currentuser sysname
select @currentuser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @currentuser, 'table', 'sys_user', 'column', 'age'

end


select @currentuser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '年龄',
   'user', @currentuser, 'table', 'sys_user', 'column', 'age'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('sys_user')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'sex')
)
begin
   declare @currentuser sysname
select @currentuser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @currentuser, 'table', 'sys_user', 'column', 'sex'

end


select @currentuser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '性别。0-未知，1-男，2-女',
   'user', @currentuser, 'table', 'sys_user', 'column', 'sex'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('sys_user')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'type')
)
begin
   declare @currentuser sysname
select @currentuser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @currentuser, 'table', 'sys_user', 'column', 'type'

end


select @currentuser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户类型。0-普通用户，99-超级管理员',
   'user', @currentuser, 'table', 'sys_user', 'column', 'type'
go

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('sys_user')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'state')
)
begin
   declare @currentuser sysname
select @currentuser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @currentuser, 'table', 'sys_user', 'column', 'state'

end


select @currentuser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '状态。0-未激活，1-激活',
   'user', @currentuser, 'table', 'sys_user', 'column', 'state'
go

